package doit20.datayi.utils

import org.apache.spark.sql.SparkSession

object UserActionProfile {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName("dsl风格api演示")
      .config("spark.sql.shuffle.partitions", 1) // spark-submit --master yarn --deploy-mode cluster --spark.sql.shuffle.partitions=400
      .master("local")
      .enableHiveSupport()
      .getOrCreate()


    spark.sql(
      """
        |with tmp1 as (
        |select
        |  *
        |from doit20dwd.app_event_detail
        |where dt between '2021-04-01' and '2021-04-30'
        |)
        |
        |select
        |guid,
        |tagname,
        |tagvalue
        |from
        |(
        |select
        |  guid,
        |  map(
        |   'pv_cnt',nvl(count(1),0),'session_cnt',nvl(count(distinct sessionid),0),
        |   'session_perday',nvl(count(distinct sessionid)/30,0),
        |   'pv_perday',nvl(count(1)/30,0)
        |  ) kvs
        |from tmp1
        |where eventid='pageView'
        |group by guid
        |) o
        |lateral view explode(kvs) mp as tagname,tagvalue
        |
        |union all
        |
        |select
        |  guid,
        |  tagname,
        |  tagvalue
        |from
        |(
        |   select
        |     guid,
        |     map(
        |     'acc_timelong_persession',nvl(avg(acc_timelong),0),
        |     'acc_timelong_perday',nvl(sum(acc_timelong)/30 ,0)
        |     ) as kvs
        |   from
        |      (
        |        select
        |         guid,
        |         sessionid,
        |         max(`timestamp`)-min(`timestamp`) as acc_timelong
        |        from tmp1
        |        group by guid,sessionid
        |      ) o1
        |   group by guid
        |) o2
        |lateral view explode(kvs) mp as tagname,tagvalue
        |
        |
        |union all
        |
        |select
        | guid,
        | eventid as tagname,
        | nvl(count(1),0) as tagvalue
        |from tmp1
        |group by guid,eventid
        |
        |limit 50
        |
        |
        |
        |
        |""".stripMargin).show(100,false)


    spark.close()
  }

}
